Shareware Grab Bag/Shareware Grab Bag.iso/019/lot103.arc/LOT103.TXT

Text File | 1985-01-01 | 16KB | 423 lines

    1-2-3 NOTES
    All Versions
    September 20, 1984
    Lotus Development Corporation

    The @IF Function

    NOTE 103


    Introduction

    The @IF function is one of 1-2-3's logical functions.  You use it
    to determine if a formula comparing two values is true or false.
    For example, @IF can compare the values in two cells; indicate if
    a formula produces a specified value; or determine if the value in
    a cell is greater than, less than, equal to, or not equal to
    another value.  When you enter an @IF function in a worksheet
    cell, 1-2-3 evaluates the formula and then displays a value in
    that cell, indicating if the formula is true or false.

    Here is a simple example of an @IF function:

           @IF(A1>B1,1,0)

    The formula compares the values in cells A1 and B1.  If the value
    in cell A1 is greater than the value in cell B1, the formula is
    true and 1 will appear in the cell where you entered the @IF
    function.  If the formula is false, that is, the value in cell A1
    is not greater than the value in cell B1, 0 will appear in the
    cell.


    Format

    The @IF function consists of the function name and three
    arguments: the condition being tested, the value if true, and the
    value if false.  The arguments must be separated by commas with no
    intervening spaces, and all three must be enclosed in parentheses.

    The following illustrates the four parts of the @IF function:


                        @IF(A10=50,1,0)

    1.@IF  2.Condition being tested  3.Value if true  4.Value if false


    1.  @IF is the function name.


    2.  The condition being tested is a formula comparing two values
        that is either true or false.  In this example, A10=50 is a
        true condition if the value in cell A10 equals 50.


    3.  The value if true is the value displayed in the cell
        containing the @IF function when the condition being tested is
        true.  In this example, 1 is displayed if the value in A10
        equals 50.


    4.  The value if false is the value displayed in the cell
        containing the @IF function when the condition being tested is
        false.  In this example, 0 is displayed if the value in A10
        does not equal 50.

    There are six ways to express the relationship between two values:

         =      equal to
         <>     not equal to
         >      greater than
         <      less than
         >=     greater than or equal to
         <=     less than or equal to

    These are called logical operators.

    You can enter values for the condition-being-tested,
    value-if-true, and value-if-false arguments in four formats:

    *  actual numbers
    *  cell addresses containing values
    *  range names that refer to single cells containing values
    *  valid formulas or functions that produce values

    Although the values 1 and 0 are frequently used as the
    value-if-true and value-if-false arguments,any values will do.
    When evaluating many values on a large worksheet, you should use
    values that stand out when you look at the worksheet (for example,
    999 or -1).

    Note:  You cannot use text or labels in an @IF function.  A label
           always has a value of 0.


    Examples

    The following examples show how values, cell references, and range
    names are used as the arguments in an @IF function.  They also
    show how the result of an @IF function changes when the values in
    the condition-being-tested argument are changed.



    Example 1

    @IF(A1>=500,1,0) means that if the value in cell A1 is greater
    than or equal to 500, the condition being tested is true and 1
    will be displayed.  If the value in cell A1 is not greater than or
    equal to 500, the condition being tested is false and 0 will be
    displayed.

           Move:  to A1
           Type:  500 [RETURN]
           Move:  to C1
           Type:  @IF(A1>=500,1,0) [RETURN]

    Cell C1, where you wrote the @IF function, should contain 1
    because the condition being tested (A1>=500) is true.

    Now change the value in cell A1 to 100.  Since the condition being
    tested is now false, cell C1 should contain a 0.


    Example 2

    @IF(B3>A3,B3,0) means that if the value in cell B3 is greater than
    the value in cell A3, the condition being tested is true and
    the value in cell B3 will be displayed.  If the value in cell B3
    is not greater than the value in cell A3, the condition being
    tested is false and 0 will be displayed.

           Move:  to A3
           Type:  100 [RETURN]
           Move:  to B3
           Type:  400 [RETURN]
           Move:  to C3
           Type:  @IF(B3>A3,B3,0) [RETURN]

    Since the condition being tested is true, you will see 400 (the
    value in cell B3) displayed in cell C3.

    Now change the value in cell A3 to 900. Since the condition being
    tested (B3>A3) is now false, 0 will replace 400 in cell C3.


    Example 3

    @IF(A5<=100,A5,999) means that if the value in cell A5 is less
    than or equal to 100, the condition being tested is true and the
    value in cell A5 will be displayed.  If the value in cell A5 is
    not less than or equal to 100, the condition being tested is false
    and 999 will be displayed.

           Move:  to A5
           Type:  75 [RETURN]
           Move:  to C5
           Type:  @IF(A5<=100,A5,999) [RETURN]

    Since the condition being tested (A5<=100) is true, you will see
    75 in cell C5.

    Now change the value in cell A5 to 200.  Since the condition being
    tested is now false, 999 will replace 75 in cell C5.


    Example 4

    This example uses range names to refer to cells containing values.

    Note:  Type range names in capital letters when you use them in
           formulas to avoid confusing them with macro commands.

    @IF(REVENUE>EXPENSES,1,-1) means that if the value in the cell
    named REVENUE is greater than the value in the cell named
    EXPENSES, 1 will be displayed.  If not, -1 will be displayed.

           Move:  to A7
           Type:  400 [RETURN]
           Move:  to B7
           Type:  100 [RETURN]

    Using the /Range Name Create command, name cell A7 REVENUE and
    cell B7 EXPENSES.

           Move:  to A7
           Select:/rnc
           Type:  REVENUE [RETURN] (two times)
           Move:  to B7
           Select:/rnc
           Type:  EXPENSES [RETURN] (two times)

    Now type the @IF function.

           Move:  to C7
           Type:  @IF(REVENUE>EXPENSES,1,-1) [RETURN]

    Since the value in cell A7, REVENUE, is greater than the value in
    cell B7, EXPENSES, the condition being tested is true and 1 will
    be displayed in cell C7.

    Now change the EXPENSES value to 500.  Since the condition being
    tested is now false, -1 will be displayed in cell C7.


    Example 5

    Assume you are the manager of a sales department and you want to
    give a 5 percent commission to every salesperson who recorded
    sales over $100.  Start with an empty worksheet and enter the
    following data, beginning in cell A1:

                    A          B         C
           1   NAME        CURR. SALE   5% COMM.
           2   Jim              3000
           3   Mike              500
           4   Susan            2000
           5   Kate               84

    This example involves two new elements: using a formula as one of
    the @IF arguments and copying the @IF function into other cells in
    the column so that it can evaluate several entries.  First, write
    an @IF function that specifies that the commission will be 0 for
    sales of $100 or less and 5 percent for any amount over $100.  The
    function should look like this:

           @IF(B2<=100,0,(B2-100)*0.05)

    Enter this function in cell C2 and copy it into cells C3, C4, and
    C5.  When you copy the function down the column, all the cell
    references are relative.  This means the function uses the
    appropriate values for each row.

    This @IF function means that if the current sales are $100 or
    less, 0 (no commission) will be displayed.  If the current sales
    are over $100, the value for 5 percent of the current sales over
    $100 will be displayed.  In this example, Jim, Mike, and Susan
    should get a 5 percent commission.  The worksheet should look like
    this:

                    A          B         C
           1   NAME        CURR. SALE   5% COMM.
           2   Jim              3000      145
           3   Mike              500       20
           4   Susan            2000       95
           5   Kate               84        0


    Example 6

    You can also use one @IF function nested inside another.  In this
    example, you will use a nested @IF function to change the
    commission structure set up in Example 5.

    Assume that in addition to the 5 percent commission for sales
    over $100, you want to give your salespeople an extra 5 percent
    commission for sales over $500.  To do this, you nest an @IF
    function in the value-if-false argument of the original formula.

    Put the label for column D in cell D1.

           Move:  to D1
           Type:  5% COMM + 5%

    Enter the following formula in cell D2 and copy it into cells D3,
    D4, and D5:

    @IF(B2<=100,0,@IF(B2<=500,(B2-100)*0.05,(B2-100)*0.05+(B2-500)*0.05))

     1.   2.    3. 4a.  4b.       4c.                4d.


    1.  @IF is the function name.

    2.  B2<=100 is the condition being tested.

    3.  0 is the value if true.

    4.  This nested @IF function is the value if false:

        a. @IF is the function name.

        b. B2<=500 is the condition being tested.

        c. (B2-100)*0.05 is the value if the nested condition is
           true.  If sales are less than or equal to $500 (B2<=500),
           the commission will be 5 percent of sales over $100.

        d. (B2-100)*0.05+(B2-500)*0.05) is the value if the nested
           condition is false.  If sales are over $500, the commission
           will be 5 percent of sales over $100 plus 5 percent of
           sales over $500.

    In this example, Jim and Susan should get an extra 5 percent
    commission for their sales over $500.  The worksheet should look
    like this:

                    A          B         C               D
           1   NAME        CURR. SALE   5% COMM.   5% COMM + 5%
           2   Jim              3000      145             270
           3   Mike              500       20              20
           4   Susan            2000       95             170
           5   Kate               84        0               0


    Compound Conditions

    You can use compound conditions in @IF functions to evaluate two
    or more conditions at the same time.  Use the following logical
    operators to build compound conditions:

           #AND#   Both conditions must be true for the result to be
                   true.

           #OR#    If either condition is true, the result is true.

           #NOT#   This logical operator uses only one condition.  If
                   the condition is true, the result will be false.
                   If the condition is false, the result will be true.


    Examples Using Compound Conditions

    Assume you own a small business and want to evaluate different
    accounts to identify those that have an outstanding balance and
    are overdue.  Start with an empty worksheet and enter the
    following information, beginning in cell A1:

                    A          B          C         D      E      F
           1    ACCOUNT   BALANCE     DAYS OVERD  #AND#   #OR#  #NOT#
           2    Thomas           $300        30
           3    Johnson          $400        80
           4    Carter           $600        90
           5    Bitler           $700        20
           6    James            $300        50


    Example 7: #AND#

    You want to identify records in which the balance is greater than
    $500 and the payment is more than 60 days overdue. Use
    #AND# in an @IF function to test for both of these conditions.
    Enter the function in column D.

           Move:  to D2
           Type:  @IF(B2>500#AND#C2>60,999,0) [RETURN]

    Now copy the function into cells D3, D4, D5, and D6.

    This @IF function means that if the value in column B (BALANCE) is
    greater than $500 and the value in column C (DAYS OVERD) is
    greater than 60, the number 999 will be displayed in column D.
    Otherwise, 0 will be displayed.


    Example 8: #OR#

    You can use the same information to select all of the records in
    which the balance is greater than $500 or the payment is more than
    60 days overdue.  Enter the function in column E.

           Move:   to E2
           Type:   @IF(B2>500#OR#C2>60,-1,0) [RETURN]

    Now copy the function into cells E3, E4, E5, and E6.

    This @IF function means that if either condition is true, -1 will
    be displayed in column E.  If both conditions are false, 0 will be
    displayed.


    Example 9: #NOT#

    #NOT# tests for only one condition. You can use it to identify
    those records in which the balance is not less than or equal to 30
    days overdue. Enter the function in column F.

           Move:   to F2
           Type:   @IF(#NOT#C2<=30,1,0) [RETURN]

    Now copy the function into cells F3, F4, F5, and F6.

    This @IF function means that if this condition is true, that is,
    if the value in column C is not less than or equal to 30, 1 will
    be displayed.  If the condition is false, 0 will be displayed.


    Now that you have completed examples 7, 8, and 9, your worksheet
    should look like this:

                   A           B          C         D     E      F
           1    ACCOUNT   BALANCE     DAYS OVERD  #AND#  #OR#  #NOT#
           2    Thomas           $300        30        0     0      0
           3    Johnson          $400        80        0    -1      1
           4    Carter           $600        90      999    -1      1
           5    Bitler           $700        20        0    -1      0
           6    James            $300        50        0     0      1


    Combining Compound Conditions

    Example 10

    You can combine compound conditions in one @IF function.  For this
    example, continue using the worksheet from Example 9.  You now
    want to identify the accounts that are both over $500 and 30 days
    overdue or that are more than 60 days overdue.  Enter the function
    in column G.

           Move:   to G2
           Type: @IF((B2>500#AND#C2>30)#OR#(B2<500#AND#C2>60),1,0)
                       [RETURN]

    Now copy the function into cells G3, G4, G5, and G6.

    If either of the conditions is true, 1 will be displayed in column
    G. If neither condition is true, 0 will be displayed.  Your
    worksheet should have 1 in cells G3 and G4 since both the Johnson
    and Carter accounts meet one of the conditions.  The Carter
    account is more than $500 and more than 30 days overdue, and the
    Johnson account is less than $500 and more than 60 days overdue.